using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Module.Common.Models;
using Module.Common.Models.Base;
using Module.Core.Common;

namespace Module.Core.DAO
{
	public class MusicManagerDAO
	{
		#region Fields

		protected string connectionStringName;

		#endregion

		#region Methods

		/// <summary>
		/// Saves a record to the MusicManager table.
		/// </summary>
		public virtual int Insert(MusicManagerInfo musicManagerInfo)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@MucsicName", musicManagerInfo.MucsicName),
				new SqlParameter("@Keyword", musicManagerInfo.Keyword),
				new SqlParameter("@Price", musicManagerInfo.Price),
				new SqlParameter("@Desciption", musicManagerInfo.Desciption),
				new SqlParameter("@Images", musicManagerInfo.Images),
				new SqlParameter("@ZoneId", musicManagerInfo.ZoneId),
				new SqlParameter("@MusicianId", musicManagerInfo.MusicianId),
				new SqlParameter("@SingerId", musicManagerInfo.SingerId),
				new SqlParameter("@PageViews", musicManagerInfo.PageViews),
				new SqlParameter("@NumberDownload", musicManagerInfo.NumberDownload),
				new SqlParameter("@Status", musicManagerInfo.Status),
				new SqlParameter("@Type", musicManagerInfo.Type),
				new SqlParameter("@CreatedBy", musicManagerInfo.CreatedBy),
				new SqlParameter("@CreatedOn", musicManagerInfo.CreatedOn),
                new SqlParameter("@GroupMusicId", musicManagerInfo.GroupMusicId),
                new SqlParameter("@Lang", musicManagerInfo.Lang),
                new SqlParameter("@FileMusic",musicManagerInfo.FileMusic),
                new SqlParameter("@ShortCode", musicManagerInfo.ShortCode),
                new SqlParameter("@CodeId", musicManagerInfo.CodeId)
			};

			return (int) SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "MusicManager_Insert", parameters);
		}

		/// <summary>
		/// Updates a record in the MusicManager table.
		/// </summary>
		public virtual void Update(MusicManagerInfo musicManagerInfo)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@MusicManageId", musicManagerInfo.MusicManageId),
				new SqlParameter("@MucsicName", musicManagerInfo.MucsicName),
				new SqlParameter("@Keyword", musicManagerInfo.Keyword),
				new SqlParameter("@Price", musicManagerInfo.Price),
				new SqlParameter("@Desciption", musicManagerInfo.Desciption),
				new SqlParameter("@Images", musicManagerInfo.Images),
				new SqlParameter("@ZoneId", musicManagerInfo.ZoneId),
				new SqlParameter("@MusicianId", musicManagerInfo.MusicianId),
				new SqlParameter("@SingerId", musicManagerInfo.SingerId),								
				new SqlParameter("@Type", musicManagerInfo.Type),
				new SqlParameter("@CreatedBy", musicManagerInfo.CreatedBy),
				new SqlParameter("@CreatedOn", musicManagerInfo.CreatedOn),
                new SqlParameter("@GroupMusicId", musicManagerInfo.GroupMusicId),
                new SqlParameter("@FileMusic",musicManagerInfo.FileMusic),
                new SqlParameter("@ShortCode", musicManagerInfo.ShortCode),
                new SqlParameter("@CodeId", musicManagerInfo.CodeId)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "MusicManager_Update", parameters);
		}

		/// <summary>
		/// Deletes a record from the MusicManager table by its primary key.
		/// </summary>
		public virtual void Delete(int musicManageId)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@MusicManageId", musicManageId)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "MusicManager_Delete", parameters);
		}

        public virtual void UpdateStatus(int musicManageId,int status)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@MusicManageId", musicManageId),
                new SqlParameter("@Status", status),
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "MusicManager_UpdateStatus", parameters);
		}
        

		/// <summary>
		/// Selects a single record from the MusicManager table.
		/// </summary>
		public virtual MusicManagerInfo Select(int musicManageId)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@MusicManageId", musicManageId)
			};

			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "MusicManager_Select", parameters))
			{
				if (dataReader.Read())
				{
					return MakeMusicManagerInfo(dataReader);
				}
				else
				{
					return null;
				}
			}
		}

		/// <summary>
		/// Selects all records from the MusicManager table.
		/// </summary>
		public CHRTList<MusicManagerInfo> SelectAll()
		{
			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "MusicManager_SelectAll"))
			{
				CHRTList<MusicManagerInfo> musicManagerInfoList = new CHRTList<MusicManagerInfo>();
				while (dataReader.Read())
				{
					MusicManagerInfo musicManagerInfo = MakeMusicManagerInfo(dataReader);
					musicManagerInfoList.Add(musicManagerInfo);
				}

				return musicManagerInfoList;
			}
		}
        public DataTable SearchAll(string keyword, int status, string zoneId, int musicianId, int singerId, int groupMusicId, int from, int to)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@Keyword",keyword),
                                              new SqlParameter("@Status",status),
                                              new SqlParameter("@zoneId",zoneId),
                                              new SqlParameter("@MusicianId",musicianId),
                                              new SqlParameter("@SingerId",singerId),
                                              new SqlParameter("@GroupMusicId",groupMusicId),
                                              new SqlParameter("@From",from),
                                              new SqlParameter("@To",to)
		};
            using (DataTable dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "MusicManager_SearchAll", parameters))
            {
                if (dt.Rows.Count > 0)
                    return dt;
                else
                {
                    return null;
                }
            }            
        }
        public DataSet ListMusicManagerPage(string keyword, int status, string zoneId, int musiCianId, int singerId, string type, DateTime createOnFrom, DateTime createOnTo, string createdBy, int currPage, int recodperPage, int pageSize, int total, string link)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@keyword",keyword),
                                              new SqlParameter("@status",status),
                                              new SqlParameter("@zoneId",zoneId),
                                              new SqlParameter("@musiCianId",musiCianId),
                                              new SqlParameter("@singerId",singerId),                                              
                                              new SqlParameter("@Type",type),
                                              new SqlParameter("@CreatedOnFrom",createOnFrom),
                                              new SqlParameter("@CreatedOnTo",createOnTo),
                                              new SqlParameter("@CreatedBy",createdBy) ,
                                              new SqlParameter("@currPage",currPage),
                                              new SqlParameter("@recodperPage",recodperPage),
                                              new SqlParameter("@pageSize",pageSize),
                                              new SqlParameter("@total",total),
                                              new SqlParameter("@link",link)
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "MusicManager_search_Page", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }
        public DataSet ListMusicManagerPage(string zoneId, string type, int currPage, int recodperPage, int pageSize, int total, string link)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {                                              
                                              new SqlParameter("@zoneId",zoneId),                                                                                     
                                              new SqlParameter("@Type",type),
                                              new SqlParameter("@currPage",currPage),
                                              new SqlParameter("@recodperPage",recodperPage),
                                              new SqlParameter("@pageSize",pageSize),
                                              new SqlParameter("@total",total),
                                              new SqlParameter("@link",link)
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "MusicManager_ViewAll_Page", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }
        public DataTable ListAll(string keyword, int status, string zoneId, int musicianId, int singerId,string type, int from, int to)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@Keyword",keyword),
                                              new SqlParameter("@Status",status),
                                              new SqlParameter("@zoneId",zoneId),
                                              new SqlParameter("@MusicianId",musicianId),
                                              new SqlParameter("@SingerId",singerId),
                                              new SqlParameter("@Type",type),
                                              new SqlParameter("@From",from),
                                              new SqlParameter("@To",to)
		};
            using (DataTable dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "MusicManager_ViewAll", parameters))
            {
                if (dt.Rows.Count > 0)
                    return dt;
                else
                {
                    return null;
                }
            }
        }

        public CHRTList<MusicManagerInfo> ListAll(string zoneId, string type, int from, int to)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@zoneId",zoneId),
                                              new SqlParameter("@Type",type),
                                              new SqlParameter("@From",from),
                                              new SqlParameter("@To",to)
		};
            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "MusicManager_list",parameters))
            {
                CHRTList<MusicManagerInfo> musicManagerInfoList = new CHRTList<MusicManagerInfo>();
                while (dataReader.Read())
                {
                    MusicManagerInfo musicManagerInfo = MakeMusicManagerInfoList(dataReader);
                    musicManagerInfoList.Add(musicManagerInfo);
                }
                return musicManagerInfoList;
            }
        }

        public CHRTList<MusicManagerInfo> Search_Casi(string keyword, int from, int to)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@Keyword",keyword),                                              
                                              new SqlParameter("@From",from),
                                              new SqlParameter("@To",to)
		};
            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "MusicManager_search_TenCasi", parameters))
            {
                CHRTList<MusicManagerInfo> musicManagerInfoList = new CHRTList<MusicManagerInfo>();
                while (dataReader.Read())
                {
                    MusicManagerInfo musicManagerInfo = MakeMusicManagerInfoList(dataReader);
                    musicManagerInfoList.Add(musicManagerInfo);
                }

                return musicManagerInfoList;
            }
        }

        public DataSet Search_TenBaiHat(string zoneId, string type, int currPage, int recodperPage, int pageSize, int total, string link)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {                                              
                                              new SqlParameter("@zoneId",zoneId),                                                                                     
                                              new SqlParameter("@Type",type),
                                              new SqlParameter("@currPage",currPage),
                                              new SqlParameter("@recodperPage",recodperPage),
                                              new SqlParameter("@pageSize",pageSize),
                                              new SqlParameter("@total",total),
                                              new SqlParameter("@link",link)
                                          };
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "MusicManager_ViewAll_Page", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }

        public DataSet Search_TenBaiHat_CaSy(string keywordMusic, string keywordSinger, int currPage, int recodperPage, int pageSize, int total, string link)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@keywordMusic",keywordMusic),
                                              new SqlParameter("@keywordSinger",keywordSinger),
                                              new SqlParameter("@currPage",currPage),
                                              new SqlParameter("@recodperPage",recodperPage),
                                              new SqlParameter("@pageSize",pageSize),
                                              new SqlParameter("@total",total),
                                              new SqlParameter("@link",link)
		};
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "MusicManager_search_TenBaiHat", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return ds;
                return null;
            }
        }
        public virtual void GetMusicManagerLates(string fileNameXML, string pathWriteXML, int lang, int topN, string zoneID, int status, string type,string type1)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@TopN", topN),
                new SqlParameter("@ZoneID", zoneID),
                new SqlParameter("@Status", status),
                new SqlParameter("@Type", type),                
                new SqlParameter("@Type1", type1),                
                new SqlParameter("@lang", lang)                
			};
            using (DataSet ds = SqlClientUtility.ExecuteDataSet(connectionStringName, CommandType.StoredProcedure, "MusicManager_Latest", parameters))
            {
                if (ds.Tables[0].Rows.Count > 0)
                    ds.WriteXml(pathWriteXML + "\\" + fileNameXML + ".xml");
                else

                    ComomFunctions.DeleteFile(pathWriteXML + "\\" + fileNameXML + ".xml");
            }
        }

        public virtual DataTable GetMusicLates(int lang, int topN, string zoneId, int status, string type,string type1)
        {            
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@TopN", topN),
                new SqlParameter("@ZoneID", zoneId),
                new SqlParameter("@Status", status),
                new SqlParameter("@Type", type),                
                new SqlParameter("@Type1", type1),                
                new SqlParameter("@lang", lang)                
			};
            using (DataTable dt = SqlClientUtility.ExecuteDataTable(connectionStringName, CommandType.StoredProcedure, "MusicManager_Latest", parameters))
            {
                return dt;
            }
        }

        public int GetCount(string keyword, int status, string zoneId, int musicianId, int singerId, string type, DateTime createOnFrom, DateTime createOnTo, string createdBy)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {
                                                new SqlParameter("@Keyword", keyword),
                                                new SqlParameter("@Status", status),
                                                new SqlParameter("@zoneId",zoneId),
                                                new SqlParameter("@MusicianId",musicianId),
                                                new SqlParameter("@SingerId",singerId),        
                                                new SqlParameter("@Type",type),                                                        
                                                new SqlParameter("@CreatedOnFrom",createOnFrom),
                                                new SqlParameter("@CreatedOnTo",createOnTo),
                                                new SqlParameter("@CreatedBy",createdBy)                                                
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "MusicManager_GetCount", parameters);

        }

        public int GetCount_BaiHat_CaSy(string keywordMuSic, string keywordSinger)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {                                                                                                
                                                new SqlParameter("@keywordMuSic",keywordMuSic),
                                                new SqlParameter("@keywordSinger",keywordSinger)
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "MusicManager_CountSearch_TenCasi", parameters);

        }

        public int GetCount(string zoneId, string type)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {                                                                                                
                                                new SqlParameter("@zoneId",zoneId),                                                                                                
                                                new SqlParameter("@Type",type)
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "MusicManager_GetCountList", parameters);

        }

		/// <summary>
		/// Creates a new instance of the MusicManager class and populates it with data from the specified SqlDataReader.
		/// </summary>
		protected virtual MusicManagerInfo MakeMusicManagerInfo(SqlDataReader dataReader)
		{
			MusicManagerInfo musicManagerInfo = new MusicManagerInfo();
			musicManagerInfo.MusicManageId = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.MUSICMANAGEID, 0);
			musicManagerInfo.MucsicName = SqlClientUtility.GetString(dataReader,DbConstants.MUSICMANAGER.MUCSICNAME, String.Empty);
			musicManagerInfo.Keyword = SqlClientUtility.GetString(dataReader,DbConstants.MUSICMANAGER.KEYWORD, String.Empty);
			musicManagerInfo.Price = SqlClientUtility.GetFloat(dataReader,DbConstants.MUSICMANAGER.PRICE, 0.0F);
			musicManagerInfo.Desciption = SqlClientUtility.GetString(dataReader,DbConstants.MUSICMANAGER.DESCIPTION, String.Empty);
			musicManagerInfo.Images = SqlClientUtility.GetString(dataReader,DbConstants.MUSICMANAGER.IMAGES, String.Empty);
			musicManagerInfo.ZoneId = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.ZONEID, 0);
			musicManagerInfo.MusicianId = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.MUSICIANID, 0);
			musicManagerInfo.SingerId = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.SINGERID, 0);
			musicManagerInfo.PageViews = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.PAGEVIEWS, 0);
			musicManagerInfo.NumberDownload = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.NUMBERDOWNLOAD, 0);
			musicManagerInfo.Status = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.STATUS, 0);
			musicManagerInfo.Type = SqlClientUtility.GetInt32(dataReader,DbConstants.MUSICMANAGER.TYPE, 0);
			musicManagerInfo.CreatedBy = SqlClientUtility.GetString(dataReader,DbConstants.MUSICMANAGER.CREATEDBY, String.Empty);
			musicManagerInfo.CreatedOn = SqlClientUtility.GetDateTime(dataReader,DbConstants.MUSICMANAGER.CREATEDON, DateTime.Now);
		    musicManagerInfo.GroupMusicId = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.GROUPMUSICID,0);
            musicManagerInfo.Lang = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.LANG, 0);
            musicManagerInfo.FileMusic = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.FILEMUSIC, String.Empty);
            musicManagerInfo.ShortCode = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.SHORTCODE, String.Empty);
            musicManagerInfo.CodeId = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.CODEID, String.Empty);
			return musicManagerInfo;
		}

        protected virtual MusicManagerInfo MakeMusicManagerInfoList(SqlDataReader dataReader)
        {
            MusicManagerInfo musicManagerInfo = new MusicManagerInfo();
            musicManagerInfo.MusicManageId = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.MUSICMANAGEID, 0);
            musicManagerInfo.MucsicName = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.MUCSICNAME, String.Empty);
            musicManagerInfo.Keyword = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.KEYWORD, String.Empty);
            musicManagerInfo.Price = SqlClientUtility.GetFloat(dataReader, DbConstants.MUSICMANAGER.PRICE, 0.0F);
            musicManagerInfo.Desciption = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.DESCIPTION, String.Empty);
            musicManagerInfo.Images = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.IMAGES, String.Empty);
            musicManagerInfo.ZoneId = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.ZONEID, 0);
            musicManagerInfo.MusicianId = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.MUSICIANID, 0);
            musicManagerInfo.SingerId = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.SINGERID, 0);
            musicManagerInfo.PageViews = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.PAGEVIEWS, 0);
            musicManagerInfo.NumberDownload = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.NUMBERDOWNLOAD, 0);
            musicManagerInfo.Status = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.STATUS, 0);
            musicManagerInfo.Type = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.TYPE, 0);
            musicManagerInfo.CreatedBy = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.CREATEDBY, String.Empty);
            musicManagerInfo.CreatedOn = SqlClientUtility.GetDateTime(dataReader, DbConstants.MUSICMANAGER.CREATEDON, DateTime.Now);
            musicManagerInfo.GroupMusicId = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.GROUPMUSICID, 0);
            musicManagerInfo.Lang = SqlClientUtility.GetInt32(dataReader, DbConstants.MUSICMANAGER.LANG, 0);
            musicManagerInfo.FileMusic = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.FILEMUSIC, String.Empty);
            musicManagerInfo.ShortCode = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.SHORTCODE, String.Empty);
            musicManagerInfo.CodeId = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.CODEID, String.Empty);
            musicManagerInfo.SingerName = SqlClientUtility.GetString(dataReader, DbConstants.MUSICMANAGER.SINGERNAME, String.Empty);
            return musicManagerInfo;
        }
		#endregion
	}
}
